package com.huellas.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.huellas.bean.ClienteBean;
import com.huellas.excepcion.DAOExcepcion;
import com.huellas.util.ConexionBD;

public class ClienteDAO extends UsuarioDAO{

	//Metodo para insertar cliente
	public void insertarCliente(ClienteBean clienteBean) throws DAOExcepcion{
		String query = "insert into cliente(ImagenPerfil, TelefonoFijo, usuario_CoUsuario) values (?,?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, clienteBean.getImagenPerfil());
			preparedStatement.setInt(2, clienteBean.getTelefonoFijo());
			preparedStatement.setInt(3, clienteBean.getCoUsuario());
					
			int i = preparedStatement.executeUpdate();
					
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
				
			if (resultSet.next()) {
				id = resultSet.getInt(1);
			}
			clienteBean.setCoUsuario(id);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
	}
	
	public void modificarCliente(ClienteBean clienteBean) throws DAOExcepcion{
		String query = "update doctor set ImagenPerfil = ?, TelefonoFijo = ? where usuario_CoUsuario = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, clienteBean.getImagenPerfil());
			preparedStatement.setInt(2, clienteBean.getTelefonoFijo());
			preparedStatement.setInt(3, clienteBean.getCoUsuario());
			
			int i = preparedStatement.executeUpdate();
			if (i != 1){
				throw new SQLException("No se pudo actualizar");
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
	}
	
	//Metodo para buscar vacunas
	public Collection<ClienteBean> buscarCliente(String NoUsuario, String ApPaterno, String ApMaterno) throws DAOExcepcion{
		String query = "select u.NoUsuario, u.ApPaterno, u.ApMaterno, u.Direccion, u.Email, u.Celular, u.NoTipoUsuario, c.ImagenPerfil, c.TelefonoFijo from Cliente c "
				       + "join (Usuario u) on (c.usuario_CoUsuario = u.CoUsuario) where u.NoUsuario like ? and u.ApPaterno like ? and u.ApMaterno like ? and u.NoTipoUsuario = 'cliente'";
		Collection<ClienteBean> listaCliente = new ArrayList<ClienteBean>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
				
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "%" + NoUsuario + "%");
			preparedStatement.setString(2, "%" + ApPaterno + "%");
			preparedStatement.setString(3, "%" + ApMaterno + "%");
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				ClienteBean clienteBean = new ClienteBean();
				clienteBean.setNoUsuario(resultSet.getString("NoUsuario"));
				clienteBean.setApPaterno(resultSet.getString("ApPaterno"));
				clienteBean.setApMaterno(resultSet.getString("ApMaterno"));
				clienteBean.setDireccion(resultSet.getString("Direccion"));
				clienteBean.setEmail(resultSet.getString("Email"));
				clienteBean.setCelular(resultSet.getInt("Celular"));
				clienteBean.setNoTipoUsuario(resultSet.getString("NoTipoUsuario"));
				clienteBean.setImagenPerfil(resultSet.getString("ImagenPerfil"));
				clienteBean.setTelefonoFijo(resultSet.getInt("TelefonoFijo"));
				listaCliente.add(clienteBean);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}	
		return listaCliente;
	}
	
	public ClienteBean obtenerCliente(int CoUsuario) throws DAOExcepcion{
		ClienteBean clienteBean = new ClienteBean();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select u.NoUsuario, u.ApPaterno, u.ApMaterno, u.Direccion, u.Email, u.Celular, u.NoTipoUsuario, c.ImagenPerfil, c.TelefonoFijo from Cliente c "
				           + "join (Usuario u) on (c.usuario_CoUsuario = u.CoUsuario) where c.usuario_CoUsuario = ?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, CoUsuario);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()){
				clienteBean.setNoUsuario(resultSet.getString("NoUsuario"));
				clienteBean.setApPaterno(resultSet.getString("ApPaterno"));
				clienteBean.setApMaterno(resultSet.getString("ApMaterno"));
				clienteBean.setDireccion(resultSet.getString("Direccion"));
				clienteBean.setEmail(resultSet.getString("Email"));
				clienteBean.setCelular(resultSet.getInt("Celular"));
				clienteBean.setImagenPerfil(resultSet.getString("ImagenPerfil"));
				clienteBean.setTelefonoFijo(resultSet.getInt("TelefonoFijo"));
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return clienteBean;
	}
	
	public ClienteBean obtenerRegistroCliente(String NoUsuario, String ApPaterno, String ApMaterno, String Email, int Celular, String NoTipoUsuario, int TelefonoFijo) throws DAOExcepcion{
		ClienteBean clienteBean = new ClienteBean();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select u.NoUsuario, u.ApPaterno, u.ApMaterno, u.Direccion, u.Email, u.Celular, u.NoTipoUsuario, c.ImagenPerfil, c.TelefonoFijo from Cliente c "
				           + "join (Usuario u) on (c.usuario_CoUsuario = u.CoUsuario) where u.NoUsuario like ? and u.ApPaterno like ? and u.ApMaterno like ? and "
				           + "u.Email like ? and u.Celular = ? and u.NoTipoUsuario like ? and c.TelefonoFijo = ?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "%" + NoUsuario + "%");
			preparedStatement.setString(2, "%" + ApPaterno + "%");
			preparedStatement.setString(3, "%" + ApMaterno + "%");
			preparedStatement.setString(4, "%" + Email + "%");
			preparedStatement.setInt(5, Celular);
			preparedStatement.setString(6, "%" + NoTipoUsuario + "%");
			preparedStatement.setInt(7, TelefonoFijo);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()){
				clienteBean.setNoUsuario(resultSet.getString("NoUsuario"));
				clienteBean.setApPaterno(resultSet.getString("ApPaterno"));
				clienteBean.setApMaterno(resultSet.getString("ApMaterno"));
				clienteBean.setDireccion(resultSet.getString("Direccion"));
				clienteBean.setEmail(resultSet.getString("Email"));
				clienteBean.setCelular(resultSet.getInt("Celular"));
				clienteBean.setImagenPerfil(resultSet.getString("ImagenPerfil"));
				clienteBean.setTelefonoFijo(resultSet.getInt("TelefonoFijo"));
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return clienteBean;
	}
}